"""
time: 2024/11/1
title: ApiFunction
message:
API 服务函数
"""
import os
import uuid
import shutil
import datetime

from Tool.SqlLink import SqlLink
from Tool.DateTime import DateTime
from Tool.StringOperation import StringOperation


class ApiFunction:
    def __init__(self):
        self.now_day = datetime.datetime.now().strftime("%Y-%m-%d")
        # 数据库链接
        self.db_link = SqlLink().get_config_link()
        # 文件默认保存位置
        self.file_save_path = "data/file/{date}/{flow_name}"
        # 文件备份路径设置
        self.file_backup_path = "data/file_backup/[ids]"

    def sid_verification(self, sid, user, ip=None, login=False):
        """
        SID验证
        :param sid:
        :param user: 用户名
        :param ip:
        :param login: 密码验证是否通过
        :return:
        """
        result = True
        # 验证SQL
        sql = f"""SELECT u.id,sk.secret,sk.link_time FROM user AS u
        LEFT JOIN secret_key AS sk ON sk.user_id=u.id
        WHERE u.id='{user}'"""
        # print(sql)
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            value = cur.fetchall()
            # 验证是否有记录
            if value:
                user_id = list(value[0])[0]
                secret = list(value[0])[1]
                tim = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                #  新建数据条目
                if secret is None:
                    # 插入数据
                    rid = uuid.uuid4().hex.replace('-', '')
                    sql = f"INSERT INTO secret_key VALUES ('{rid}', '{user_id}', '{sid}', '{ip}', '{tim}')"
                    cur.execute(sql)
                    con.commit()
                # 长时间无数据交互
                elif secret == sid:
                    # 计算时差
                    time_dif = DateTime().time_difference(start=list(value[0])[2])
                    # 有效期 8 小时
                    if time_dif['M'] > 480:
                        print("有效期大于8小时")
                        result = False
                    else:
                        # 更新数据条目
                        sql = f"UPDATE secret_key SET link_time='{tim}' WHERE `user_id`='{user_id}'"
                        cur.execute(sql)
                        con.commit()
                # 密码验证通过
                elif login:
                    sql = f"""UPDATE secret_key SET secret="{str(sid)}", link_time='{tim}'
                     WHERE `user_id`='{user_id}'"""
                    # print(f"更新SID {sql}")
                    cur.execute(sql)
                    con.commit()
                else:
                    print("SID 无法判断")
                    result = False
            else:
                print("SID 未查询到任何数据")
                result = False
        # TODO 区分开发与生产
        return result

    def sid_check(self, sid):
        """
        验证SID
        :param sid:
        :return:
        """
        try:
            # 哨兵专用
            if sid == "bcdefghijklmnopqrstuvwxyz{23456789:101":
                return True
            # 解密SID
            user, ip, tim = StringOperation().decrypt(sid).split('|')
            # print(user, ip, tim)
            # 验证SID
            sid_ver = self.sid_verification(sid=sid, user=user, ip=ip)
            # print(sid_ver)
            if sid_ver:
                return True
            else:
                return False
        except BaseException as e:
            print(f"获取到异常的 sid [{sid}]\n异常摘要[{e}]")
            return False

    @staticmethod
    def list_dict(font, data):
        """
        列表转为字典
        :param font:
        :param data:
        :return:
        """
        return [dict(zip(font, row)) for row in data]

    def login(self, user, pwd, ip):
        """
        验证登录信息
        :param user:
        :param pwd:
        :param ip:
        :return:
        """
        result = {"status": False, "msg": "OK", "sid": "", "user_name": "", "group": ""}
        sql = f"SELECT id,pwd,name,grouping FROM user WHERE `user`='{user}'"
        tim = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            if not values:
                result['msg'] = "账号不存在/信息错误"
            else:
                font = ['user_id', 'pwd', 'name', 'grouping']
                json_result = [dict(zip(font, row)) for row in values][0]
                # 验证 密码是否相同
                if str(pwd) == json_result['pwd']:
                    result['status'] = True
                    # 生成sid
                    result['sid'] = StringOperation().encrypt(f"{json_result['user_id']}|{ip}|{tim}")
                    # 验证SID
                    self.sid_verification(sid=result['sid'], user=json_result['user_id'], ip=ip, login=True)
                    # 添加分组
                    result['group'] = json_result['grouping']
                    # 添加用户名
                    result['user_name'] = json_result['name']
                    result['user_id'] = json_result['user_id']
                else:
                    result['msg'] = "账号不存在/密码错误"
        return result

    def work_status(self, sid, robot_id, ip, status, page, limit):
        """
        机器人工作状态查询
        :param sid:
        :param robot_id: 机器人ID
        :param status: 机器人状态
        :param ip: 机器人IP地址
        :param page: 页面值
        :param limit: 页面数量
        :return:
        """
        result = {'status': False, "count": 0, "data": [], "msg": "ok"}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = "SID 已过期/无效"
            return result
        else:
            result['status'] = True
            result['sid'] = sid

        where = ""
        # 查询条件设置
        if robot_id != "" and robot_id is not None:
            where += f" id = '{robot_id}'"
        if status != "" and robot_id is not None:
            if where == "":
                where += f" status='{status}'"
            else:
                where += f" AND status='{status}'"
        if ip != '':
            if where == "":
                where += f" ip ='{ip}'"
            else:
                where += f" AND ip ='{ip}'"
        # id,ip,name,status,health,user_name,remark
        spek = (int(page) - 1) * int(limit)
        sql = f"SELECT * FROM robot_info {f'WHERE {where}' if where != '' else ''} LIMIT {int(limit)} OFFSET {spek}"
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            value = cur.fetchall()
            if not value:
                result['msg'] = "未获取到相关数据"
            else:
                # 转换字典
                font = ['robot_id', 'ip', 'name', 'status', 'health', 'user_name', 'remark']
                result['data'] = self.list_dict(font, value)
                # 获取总数据量
                sql = f"SELECT COUNT(*) FROM robot_info {f'WHERE {where}' if where != '' else ''}"
                cur.execute(sql)
                value = cur.fetchall()
                result['count'] = list(value[0])[0]
            result['status'] = True
        return result

    def robot_info_set(self, sid, robot, ip, name, status, health, username, remark):
        """
        机器人信息修改
        :param sid:
        :param robot:
        :param ip:
        :param name:
        :param status:
        :param health:
        :param username:
        :param remark:
        :return:
        """
        result = {'status': True, 'msg': 'OK'}
        # 验证sid
        if not self.sid_check(sid):
            result = {'status': False, 'msg': 'SID 已过期/无效', 'sid': sid}
            return result
        else:
            result['sid'] = sid
        try:
            # 获取所有机器人ID
            sql = "SELECT id FROM robot_info"
            with self.db_link as con:
                cur = con.cursor()
                cur.execute(sql)
                id_list = cur.fetchall()
                if not id_list:
                    result['msg'] = "机器人信息无效"
                else:
                    robot_id = [list(cell)[0] for cell in id_list]
                    if robot not in robot_id:
                        result['msg'] = "机器人信息无效"
                    else:
                        set_list = []
                        if ip != '':
                            set_list.append(f"ip='{ip}'")
                        if name != '':
                            set_list.append(f"name='{name}'")
                        if status != '':
                            set_list.append(f"status='{status}'")
                        if health != '':
                            set_list.append(f"health='{health}'")
                        if username != '':
                            set_list.append(f"user_name='{username}'")
                        if remark != '':
                            set_list.append(f"remark='{remark}'")
                        if set_list:
                            set_value = ','.join(set_list)
                            sql = f"""UPDATE robot_info SET {set_value} WHERE id='{robot}'"""
                            cur.execute(sql)
                            con.commit()
                        else:
                            result['msg'] = "未设置修改信息"
        except BaseException as e:
            print(e)
            result['msg'] = "服务器处理数据信息时发生异常"
        return result

    def robot_info_new(self, sid, ip, name, status, health, username, remark):
        """
        机器人信息新增
        :param sid:
        :param ip:
        :param name:
        :param status:
        :param health:
        :param username:
        :param remark:
        :return:
        """
        result = {'status': False, 'msg': 'OK;'}
        # 验证sid
        if not self.sid_check(sid):
            result = {'status': False, 'msg': 'SID 已过期/无效', 'sid': sid}
            return result
        else:
            result['sid'] = sid

        tim = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # 默认信息处理
        if ip == '':
            result['msg'] = "机器人IP信息无效"
        elif name == '':
            result['status'] = True
            result['msg'] += '机器人名称未设置，将采用IP代替设备名称进行记录;'
            name = ip
        elif username == '':
            result['status'] = True
            result['msg'] += '机器人使用人未设置，将默认为RPA;'
            username = 'RPA'
        elif remark == '':
            result['status'] = True
            result['msg'] += '机器人备注信息未设置，将默认为当前创建信息;'
            remark = f"本设备信息记录于{tim}"
        else:
            result['status'] = True

        if result['status']:
            rid = uuid.uuid4().hex.replace('-', '')
            sql = f"""INSERT INTO robot_info (id, ip, name, status, health, user_name, remark) VALUES (
            '{rid}', '{ip}', '{name}', '{status}', '{health}', '{username}', '{remark}')"""
            with self.db_link as con:
                cur = con.cursor()
                cur.execute(sql)
                con.commit()
                result['status'] = True
                result['robot_id'] = rid

        return result

    def robot_info_del(self, sid, robot_id):
        """
        机器人信息删除
        :param sid:
        :param robot_id:
        :return:
        """
        result = {'status': False, 'msg': 'OK;'}
        # 验证sid
        if not self.sid_check(sid):
            result = {'status': False, 'msg': 'SID 已过期/无效', 'sid': sid}
            return result
        else:
            result['sid'] = sid

        with self.db_link as con:
            cur = con.cursor()
            cur.execute(f"DELETE FROM robot_info WHERE id='{robot_id}'")
            con.commit()
        result['status'] = True
        return result

    def quest_list(self, sid, robot_id, flow_id, flow_name, page, limit):
        """
        任务列表处理
        :param sid:
        :param robot_id:
        :param flow_id:
        :param flow_name:
        :param page:
        :param limit:
        :return:
        """
        result = {'status': False, 'msg': 'OK;', 'count': 0, 'data': []}
        # 验证sid
        if not self.sid_check(sid):
            result = {'status': False, 'msg': 'SID 已过期/无效', 'sid': sid}
            return result
        else:
            result['sid'] = sid

        # 验证信息
        where_list = []
        # 根据机器人筛选
        if robot_id != '':
            where_list.append(f"fi.robot_id = '{robot_id}'")
        # 根据流程ID筛选
        if flow_id != '':
            where_list.append(f"fi.id = '{flow_id}'")
        # 根据流程名称筛选
        if flow_name != '':
            where_list.append(f"fi.flow_name LIKE '%{flow_name}%'")
        if len(where_list) == 0:
            where = ''
        else:
            where = ' AND '.join(where_list)
        # 查询数据
        skip = (int(page) - 1) * int(limit)
        with self.db_link as con:
            cur = con.cursor()
            sql = f"""SELECT fi.id, fi.robot_id, fi.mrpa_id, fi.flow_name, ri.name AS robot_name,
            IFNULL(fi.status, 2)AS flow_status
            FROM flow_info AS fi
            LEFT JOIN robot_info AS ri ON ri.id = fi.robot_id
            {f'WHERE {where}' if where != '' else ''} LIMIT {int(limit)} OFFSET {skip}"""
            cur.execute(sql)
            values = cur.fetchall()
            result['status'] = True
            if not values:
                result['msg'] = "未获取到有效数据"
            else:
                font = ['flow_id', 'robot_id', 'mrpa_id', 'flow_name', 'robot_name', 'flow_status']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                # 获取数量值
                sql = f"""SELECT COUNT(*) FROM flow_info AS fi
                LEFT JOIN robot_info AS ri ON fi.robot_id = ri.id {f'WHERE {where}' if where != '' else ''}"""
                cur.execute(sql)
                result['count'] = list(cur.fetchall()[0])[0]
                # 获取任务名称列表信息
                get_sql = "SELECT id, flow_name FROM flow_info GROUP BY flow_name"
                cur.execute(get_sql)
                result['flow_list'] = [dict(zip(['flow_id', 'flow_name'], row)) for row in cur.fetchall()]
        return result

    def flow_info_add(self, sid, robot_id, mrpa_id, flow_name, file_path, file_md5, grouping, status):
        """
        流程信息新增
        :param sid:
        :param robot_id:
        :param mrpa_id:
        :param flow_name:
        :param file_path:
        :param file_md5:
        :param grouping:
        :param status:
        :return:
        """
        result = {'status': False, 'msg': 'OK'}
        # 验证sid
        if not self.sid_check(sid):
            result = {'status': False, 'msg': 'SID 已过期/无效', 'sid': sid}
            return result
        else:
            result['sid'] = sid
        if robot_id is None:
            result['msg'] += '机器人ID未正确设置'
        if mrpa_id is None:
            result['msg'] += '机器人mrpa_id未正确设置'
        if flow_name is None:
            result['msg'] += '流程名称未设置'
        if file_path is None:
            result['msg'] += '流程文件路径未设置'
        if grouping is None:
            result['msg'] += '流程分组未设置, 将采用默认分组【通用】'
            grouping = '通用'
        if status not in [0, 1, 2]:
            result['msg'] += f'流程状态信息错误,[0,1,2]√ [{status}]×'
            status = 2
        if result['msg'] != 'OK':
            return result
        flow_id = uuid.uuid4().hex.replace('-', '')
        sql = f"""INSERT INTO flow_info (id, robot_id, mrpa_id, flow_name, file_path, file_md5, grouping, status)
        VALUES ('{flow_id}', '{robot_id}', '{mrpa_id}', '{flow_name}', '{file_path}', '{file_md5}', '{grouping}', '{status}')"""
        with self.db_link as con:
            cur = con.cursor()
            # 查重
            se_sql = f"SELECT COUNT(*) FROM flow_info WHERE robot_id='{robot_id}' AND mrpa_id='{mrpa_id}'"
            cur.execute(se_sql)
            count = list(cur.fetchall()[0])[0]
            if count > 0:
                result['msg'] = '机器人已存在该流程，请勿重复添加'
            else:
                cur.execute(sql)
                con.commit()
                result['status'] = True
                result['flow_id'] = flow_id
                result['msg'] = '添加成功'
        return result

    def execution_history(self, sid, flow_id, robot_id, user_id, status, start_time, stop_time, progress, group,
                          flow_name, page, limit):
        """
        执行历史处理
        :param flow_name:
        :param sid:
        :param flow_id:
        :param robot_id:
        :param user_id:
        :param status:
        :param start_time:
        :param stop_time:
        :param progress:
        :param group:
        :param page:
        :param limit:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'count': 0, 'data': [], 'sid': ''}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result

        # 整合SQL条件
        where_list = []
        if flow_id != '':
            where_list.append(f"fi.id='{flow_id}'")
        if robot_id != '':
            where_list.append(f"fh.robot_id='{robot_id}'")
        if user_id != '':
            where_list.append(f"u.user_id='{user_id}'")
        if status != '':
            where_list.append(f"fh.status = '{status}'")
        if start_time != '':
            where_list.append(f"fh.start_time LIKE '%{start_time}%'")
        if stop_time != '':
            where_list.append(f"fh.stop_time LIKE '%{stop_time}%'")
        if flow_name != '':
            where_list.append(f"fi.flow_name LIKE '%{flow_name}%'")
        if progress == '':
            # TODO 获取前一天的数据
            where_list.append(f"fh.progress='{datetime.datetime.now().strftime('%Y-%m-%d')}'")
        else:
            where_list.append(f"fh.progress='{progress}'")
        if group != '':
            where_list.append(f"fh.grouping='{group}'")

        if where_list:
            where = "WHERE " + ' AND '.join(where_list)
        else:
            where = ""
        skip = (int(page) - 1) * int(limit)
        sql = f"""SELECT
          u.id AS user_ids,
          u.name AS user_names,
          ri.id AS robot_ids,
          ri.ip,
          ri.name AS robot_name,
          fi.flow_name,
          fi.id AS flow_id,
          fi.mrpa_id AS mrpa_id,
          fh.id,
          fh.status,
          fh.start_time,
          fh.stop_time,
          fh.progress,
          fh.message,
          fh.grouping,
          fh.remake,
          fh.work_flow_id 
        FROM
          flow_history AS fh
          LEFT JOIN flow_info AS fi ON fi.id = fh.flow_id
          LEFT JOIN robot_info AS ri ON fh.robot_id = ri.id
          LEFT JOIN user AS u ON fh.user_id = u.id {where if where != '' else ''} LIMIT {int(limit)} OFFSET {skip}"""

        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            result['status'] = True
            if not values:
                result['msg'] = "未查询到任何有效数据"
            else:
                font = ['user_id', 'user_name', 'robot_id', 'ip', 'robot_name', 'flow_name', 'flow_id', 'mrpa_id', 'id',
                        'status', 'start_time', 'stop_time', 'progress', 'message', 'grouping', 'remake', 'work_flow_id']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                # 获取总量
                sql = f"""SELECT COUNT(*) FROM flow_history AS fh
                  LEFT JOIN flow_info AS fi ON fi.id = fh.flow_id
                  LEFT JOIN robot_info AS ri ON fh.robot_id = ri.id
                  LEFT JOIN user AS u ON fh.user_id = u.id {where if where != '' else ''}"""
                cur.execute(sql)
                result['count'] = list(cur.fetchall()[0])[0]
        return result

    def start_flow(self, sid, flow_id, flow_name, robot_id, user_id):
        """
        开始执行流程
        :param sid:
        :param flow_id:
        :param flow_name:
        :param robot_id:
        :param user_id:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'sid': sid, 'history_id': ''}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        if flow_id == '' and flow_name == '':
            result['msg'] = "未获取到有效参数"
            return result
        with self.db_link as con:
            cur = con.cursor()
            sql = f"SELECT COUNT(*) FROM flow_info WHERE id = '{flow_id}' OR flow_name = '{flow_name}'"
            cur.execute(sql)
            count = cur.fetchall()
            if list(count[0])[0] == 1:
                ids = uuid.uuid4().hex.replace("-", "")
                sql = f"""INSERT INTO flow_history (id, flow_id, robot_id, user_id, grouping, message, start_time, 
                progress) 
                VALUES ('{ids}','{flow_id}', '{robot_id}', '{user_id}', '待执行', '发起执行', 
                '{DateTime.get_now_time_str()}', '{DateTime.get_now_time_str("%Y-%m-%d")}')"""
                # print(sql)
                cur.execute(sql)
                con.commit()
                result['status'] = True
                result['history_id'] = ids
            else:
                result['msg'] = '流程不存在/启动参数错误/参数无效/无记录数据/参数指向错误/参数多重指向'

        return result

    def stop_flow(self, sid, robot_id, flow_id):
        """
        停止在 某台 设备上 某个流程
        :param sid:
        :param robot_id:
        :param flow_id:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'sid': sid}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        now_time = DateTime.get_now_time_str("%Y-%m-%d")
        with self.db_link as con:
            cur = con.cursor()
            sql = f"""SELECT COUNT(*) FROM flow_history 
            WHERE robot_id = '{robot_id}' AND flow_id = '{flow_id}' AND progress = '{now_time}'"""
            cur.execute(sql)
            print(sql)
            count = cur.fetchall()
            if list(count[0])[0] >= 1:
                sql = f"""UPDATE flow_history SET stop_time='{now_time}', progress='{now_time}', message='已停止', 
                status = '停止执行', grouping='待停止'
                    WHERE robot_id='{robot_id}' AND flow_id='{flow_id}' AND progress = '{now_time}' 
                    AND grouping='运行中' """
                cur.execute(sql)
                con.commit()
                result['status'] = True
            else:
                result['msg'] = '无有效数据'
        return result

    def flow_running_record(self, sid, flow_id, progress, flow_id_list, flow_name, robot_id):
        """
        查询流程进度信息
        :param sid:
        :param flow_id:
        :param progress:
        :param flow_id_list:
        :param flow_name:
        :param robot_id:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'data': []}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid
        is_up = False
        sql = "SELECT id, progress FROM flow_info"
        if flow_id != '':
            sql = f"SELECT id, progress FROM flow_info WHERE id = '{flow_id}'"
        if flow_id_list:
            if len(flow_id_list) == 1:
                sql = f"SELECT id, progress FROM flow_info WHERE id = '{flow_id_list[0]}'"
            else:
                sql = f"SELECT id, progress FROM flow_info WHERE id IN {tuple(flow_id_list)}"
        if progress != '' and flow_id != '':
            sql = f"UPDATE flow_info SET progress = '{progress}' WHERE id = '{flow_id}'"
            is_up = True
        elif progress != '' and flow_name != '' and robot_id != '':
            # TODO 需要验证机器人ID
            sql = (f"UPDATE flow_info SET progress = '{progress}' WHERE flow_name = '{flow_name}' "
                   f"AND robot_id = '{robot_id}'")
            is_up = True

        # print(sql)
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            result['status'] = True
            if not values:
                result['msg'] = "数据处理结果未返回"
                if is_up:
                    result['msg'] = "数据更新成功"
                    con.commit()
            else:
                font = ['id', 'progress']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                result['count'] = len(value)
        return result

    def file_upload(self, sid, ids, flow_id, file_name):
        """
        文件上传信息
        :param sid:
        :param ids:
        :param flow_id:
        :param file_name:
        :return:
        """
        # 验证参数
        result = {'status': False, 'msg': 'OK', 'data': []}
        # 验证sid
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid

        # 判断参数
        if file_name == '':
            result['msg'] = '参数错误, 请传递正确的文件名称'
            return result
        flow_name = self.get_flow_names(flow_id)
        if flow_name == '流程未知获取失败':
            result['msg'] = '参数错误, 请传递正确的流程ID'
            return result
        # 新建文件
        if ids == '':
            ids = uuid.uuid4().hex.replace("-", "")
            # 拆分文件名称和文件类型。
            file_type = file_name.split('.')[-1]
            # 设置文件保存路径(file_path)
            root_path = self.file_save_path.format(date=self.now_day, flow_name=flow_name)
            save_path = os.path.normpath(os.path.join(root_path, file_name))
            # 处理文件路径
            if not os.path.exists(root_path):
                os.makedirs(root_path)

            # 写入数据进行记录
            # TODO 需要处理备份文件路径
            backup_path = self.file_backup_path.replace('[ids]', ids)
            sql = f"""INSERT INTO file_info (id, flow_id, file_name, file_type, save_path, save_type, progress, 
                backup_path)
                VALUES ('{ids}', '{flow_id}', '{file_name}', '{file_type}', '{save_path}', 'local', '{self.now_day}', 
                '{backup_path}')"""
            # 记录信息
            with self.db_link as con:
                cur = con.cursor()
                cur.execute(sql)
                con.commit()
            # 设置返回信息
            result['data'].append({'file_id': ids, 'flow_id': flow_id, 'file_name': file_name, 'file_type': file_type,
                                   'save_path': save_path, 'save_type': 'SERVER'})
            result['status'] = True
            result['server_path'] = save_path
        # 更新文件
        else:
            # 查询文件备份路径及文件名称
            with self.db_link as con:
                cur = con.cursor()
                sql = f"SELECT backup_path, file_name, save_path FROM file_info WHERE id = '{ids}'"
                cur.execute(sql)
                values = cur.fetchall()
                if values:
                    font = ['backup_path', 'file_name', 'save_path']
                    value = [dict(zip(font, row)) for row in values]
                    backup_path = value[0]['backup_path']
                    filename = value[0]['file_name']
                    save_p = value[0]['save_path']
                    now_time = DateTime.get_now_time()
                    back_path = os.path.join(backup_path, f"{now_time}_{filename}")
                    # 备份文件路径处理
                    if not os.path.isdir(backup_path):
                        os.makedirs(backup_path)
                    # 移动文件至备份路径中
                    if os.path.isfile(save_p):
                        shutil.move(save_p, back_path)
                        result['msg'] = "文件备份成功"
                    else:
                        result['msg'] = "文件不存在, 新文件已存放"
                    # 拼接新的文件
                    _file_type = file_name.split('.')[-1]
                    root_path = self.file_save_path.format(date=self.now_day, flow_name=flow_name)
                    if not os.path.isdir(root_path):
                        os.makedirs(root_path)
                    server_path = os.path.normpath(os.path.join(root_path, file_name))
                    # 更新数据库
                    sql = f"""UPDATE file_info SET save_path = '{server_path}',file_type='{_file_type}', 
                        progress = '{self.now_day}', backup='{backup_path}', file_name='{file_name}' WHERE id = '{ids}'"""
                    cur.execute(sql)
                    con.commit()
                    # 设置返回信息
                    res_val = {'save_path': server_path, 'backup_path': backup_path, 'file_name': file_name,
                               'file_type': _file_type}
                    result['status'] = True
                    result['data'].append(res_val)
                    result['server_path'] = server_path
                else:
                    result['msg'] = "文件参数无效"

        return result

    def get_flow_names(self, flow_id):
        """
        获取流程名称
        :param flow_id:
        :return:
        """
        result = "流程未知获取失败"
        sql = f"SELECT flow_name FROM flow_info WHERE id = '{flow_id}'"
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            if values:
                font = ['flow_name']
                value = [dict(zip(font, row)) for row in values]
                result = value[0]['flow_name']
        return result

    def file_history(self, sid, file_id, file_name, flow_id, date, page, limit, flow_name):
        """
        查询文件上传历史
        :param sid:
        :param file_id:
        :param file_name:
        :param flow_id:
        :param date:
        :param page:
        :param limit:
        :param flow_name:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'data': [], 'count': 0}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid
        # 参数处理
        where = ""
        if file_id != '':
            where = f"id = '{file_id}'"
        if file_name != '':
            where += f"file_name LIKE '%{file_name}%'"
        if flow_id != '':
            where += f"flow_id = '{flow_id}'"
        if flow_name != '':
            where += f"flow_name LIKE '%{flow_name}%'"
        if date != '':
            where += f"file.progress = '{date}'"
        if where == "":
            where = f"file.progress LIKE '%{self.now_day[:-3]}%'"

        skip = (int(page) - 1) * int(limit)
        sql = f"""SELECT file.id,
        flow_id, file_name, file_type, save_path, save_type, file.progress, backup, backup_path, flow_name
        FROM file_info AS file
        LEFT JOIN flow_info AS flow ON flow.id = file.flow_id WHERE {where} LIMIT {int(limit)} OFFSET {skip}"""
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            result['status'] = True
            if values:
                font = ['id', 'flow_id', 'file_name', 'file_type', 'save_path', 'save_type', 'progress', 'backup',
                        'backup_path', 'flow_name']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                # 获取总数据量
                sql = f"""SELECT COUNT(*) FROM file_info AS file
                        LEFT JOIN flow_info AS flow ON flow.id = file.flow_id WHERE {where}"""
                cur.execute(sql)
                result['count'] = list(cur.fetchall()[0])[0]
            else:
                result['msg'] = "未获取到有效数据信息"
        return result

    def del_file(self, sid, file_id, file_id_list, file_name):
        """
        删除文件
        :param sid:
        :param file_id:
        :param file_id_list:
        :param file_name:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'data': [], 'count': 0}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid

        if file_id != '':
            sql = f"SELECT save_path FROM file_info WHERE id = '{file_id}'"
        elif file_id_list:
            sql = f"SELECT save_path FROM file_info WHERE id IN {tuple(file_id_list)}"
        elif file_name != '':
            sql = f"SELECT save_path FROM file_info WHERE file_name LIKE '%{file_name}%'"
        else:
            result['msg'] = "参数无效"
            return result
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            if values:
                result['status'] = True
                for row in values:
                    file_path = list(row)[0]
                    if os.path.isfile(file_path):
                        os.remove(file_path)
                        result['data'].append({'save_path': file_path, 'msg': "文件删除成功", 'status': 0})
                        result['msg'] = "文件删除成功"
                    else:
                        result['data'].append({'save_path': file_path, 'msg': "文件不存在", 'status': 1})
                # 删除文件记录
                if file_id != '':
                    sql = f"DELETE FROM file_info WHERE id = '{file_id}'"
                elif file_id_list:
                    sql = f"DELETE FROM file_info WHERE id IN {tuple(file_id_list)}"
                cur.execute(sql)
                con.commit()
            else:
                result['msg'] = "未获取到有效数据信息"
        return result

    def user_all(self, sid, user_id, page, limit):
        """
        获取用户列表
        :param sid:
        :param user_id:
        :param page:
        :param limit:
        :return:
        """
        result = {'status': False, 'msg': 'OK', 'data': [], 'count': 0}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        result['sid'] = sid
        skip = (int(page) - 1) * int(limit)
        if user_id != '':
            where = f"WHERE `id` = '{user_id}'"
        else:
            where = ''
        sql = f"""SELECT id, user, pwd, name, grouping, creattime FROM user {where} LIMIT {int(limit)} OFFSET {skip}"""
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            values = cur.fetchall()
            if values:
                font = ['id', 'user', 'pwd', 'name', 'grouping', 'create_time']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                # 获取总数据量
                sql = f"""SELECT COUNT(*) FROM user"""
                cur.execute(sql)
                result['count'] = list(cur.fetchall()[0])[0]
                result['status'] = True
        return result

    def user_new(self, sid, user, pwd, name, grouping):
        """
        用户新增
        :param sid:
        :param user:
        :param pwd:
        :param name:
        :param grouping:
        :return:
        """
        result = {'status': False, 'msg': 'OK'}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid
        if user == '':
            result['msg'] = '用户名不能为空'
            return result
        if pwd == '':
            result['msg'] = '密码不能为空'
            return result
        if name == '':
            result['msg'] = '用户昵称不能为空'
            return result
        if grouping == '':
            result['msg'] = '用户组为空, 将默认分配到普通用户组'
            grouping = '普通用户'
        # FIXME 账号重复问题需要处理
        user_id = uuid.uuid4().hex.replace('-', '')
        now_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sql = f"""INSERT INTO user(id, user, pwd, name, grouping, creattime) 
        VALUES ('{user_id}','{user}', '{pwd}', '{name}', '{grouping}', '{now_time}')"""
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            con.commit()
            result['msg'] = "用户新增成功"
            result['status'] = True
            result['data'] = [
                {'id': user_id, 'user': user, 'password': '*' * len(pwd), 'name': name, 'grouping': grouping}
            ]
        return result

    def user_set(self, sid, user_id, user, pwd, name, grouping):
        """
        用户信息修改
        :param sid:
        :param user_id:
        :param user:
        :param pwd:
        :param name:
        :param grouping:
        :return:
        """
        result = {'status': False, 'msg': 'OK'}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid

        if user_id != '':
            where = f"id = '{user_id}'"
        else:
            result['msg'] = '用户ID为空'
            return result

        set_list = list()
        if user != '' or user is not None:
            set_list.append(f"user = '{user}'")
        if pwd != '':
            set_list.append(f"pwd = '{pwd}'")
        if name != '':
            set_list.append(f"name = '{name}'")
        if grouping != '':
            set_list.append(f"grouping = '{grouping}'")
        if not set_list:
            result['msg'] = '未设置任何修改项'
            return result
        set_sql = ','.join(set_list)
        sql = f"UPDATE user SET {set_sql} WHERE {where}"
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql)
            con.commit()
            result['msg'] = "用户信息修改成功"
            result['status'] = True
        return result

    def user_del(self, password, user, sid):
        """
        删除用户信息
        :param password:
        :param user:
        :param sid:
        :return:
        """
        result = {'status': False, 'msg': 'OK'}
        if not self.sid_check(sid):
            result['msg'] = 'SID 已过期/无效'
            return result
        else:
            result['sid'] = sid
        if password == '' or user == '':
            result['msg'] = '密码/用户名 其中一项信息错误或者无效'
            return result
        with self.db_link as con:
            cur = con.cursor()
            sql = f"SELECT id, user, name, grouping FROM user WHERE pwd = '{password}' AND user = '{user}'"
            cur.execute(sql)
            values = cur.fetchall()
            if values:
                font = ['id', 'user', 'name', 'grouping']
                value = [dict(zip(font, row)) for row in values]
                result['data'] = value
                result['status'] = True
                del_sql = f"DELETE FROM user WHERE id = '{password}' AND user = '{user}'"
                cur.execute(del_sql)
                con.commit()
                result['status'] = True
                result['msg'] = "用户信息删除成功"
            else:
                result['msg'] = "未获取到有效用户信息"
        return result

    def api_sql_runner(self, sid, sql_lang, font_list=None):
        """
        执行SQL
        :param sid:
        :param sql_lang:
        :param font_list:
        :return:
        """
        if font_list is None:
            font_list = []
        result = {'status': False, 'data': [], 'font': []}
        with self.db_link as con:
            cur = con.cursor()
            cur.execute(sql_lang)
            values = cur.fetchall()
            con.commit()
            result['status'] = True
            if values:
                result['count'] = len(values)
                result['msg'] = f"获取到[{len(values)}]条数据"
                if font_list is None or font_list == []:
                    result['data'] = values
                else:
                    value = [dict(zip(font_list, row)) for row in values]
                    result['data'] = value
            else:
                result['msg'] = "未查询到数据"
        return result
